﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using AcadPos.Entity;
using TrustX.Dados;
using System.Data.SqlClient;
using System.Data;

namespace AcadPos.Data
{
    public class DBUsuario
    {
        #region Listar Usuários
        public DataTable listarUsuarios(string tpPesquisa, Usuario usuario)
        {
            string SQL = @"SELECT tu.ID_USUARIO,
                                   tu.NOME,
                                   tu.EMAIL,    
                                   CASE                                   
                                   tu.SITUACAO
                                   WHEN 'A' THEN 'ATIVO'
                                   ELSE 'INATVO'END AS SITUACAO,
                                   tu.LOTACAO
                            FROM   TB_USUARIO tu";

            if (tpPesquisa.Equals("c"))
                SQL += " WHERE tu.ID_USUARIO = @codigo ";
            else if (tpPesquisa.Equals("n"))
                SQL += " WHERE tu.NOME LIKE '%' + @nome + '%' ";

            SQL += " ORDER BY tu.NOME";

            DataAccess db = new DataAccess(SQL);
            if (tpPesquisa.Equals("c"))
                db.AddParam("@codigo", SqlDbType.Int, usuario.Codigo);
            else if (tpPesquisa.Equals("n"))
                db.AddParam("@nome", SqlDbType.VarChar, usuario.Nome);
            return db.GetDataTable();
        }
        #endregion

        #region Carregar Dados do Usuário
        public Usuario carregarDadosUsuario(int pCodigo)
        {
            string SQL = @"SELECT tu.ID_USUARIO,
                                   tu.NOME,
                                   tu.EMAIL,
                                   tu.SITUACAO,
                                   tu.LOTACAO
                            FROM   TB_USUARIO tu
                            WHERE tu.ID_USUARIO = @ID_USUARIO";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_USUARIO", SqlDbType.Int, pCodigo);
            try
            {
                db.AbrirConexao();
                SqlDataReader dr = db.ExecuteReader();
                Usuario usuario = new Usuario();
                if (dr.Read())
                {
                    usuario.Codigo = pCodigo;
                    usuario.Nome = dr["NOME"].ToString();
                    usuario.Email = dr["EMAIL"].ToString();
                    usuario.Status = dr["SITUACAO"].ToString();
                    usuario.Lotacao = dr["LOTACAO"].ToString();
                }
                return usuario;
            }
            catch
            {
                return null;
            }
        }
        #endregion

        #region Inserir Usuário
        public bool inserirUsuario(Usuario usu, string usuarioLogado)
        {
            string sql = "";
            DataAccess db = new DataAccess(sql);
            SqlTransaction transacao;
            db.AbrirConexao();
            transacao = db.BeginTransaction();
            try
            {
                db.Command.Transaction = transacao;
                #region Inserir Usuário
                string SQL = @"INSERT INTO TB_USUARIO
                                   (NOME,
                                    EMAIL,
                                    SITUACAO,
                                    LOTACAO,
                                    DATA_CADASTRO)
                             VALUES
                                   (@NOME,
                                   @EMAIL,
                                   @SITUACAO, 
                                   @LOTACAO,
                                   GETDATE())";

                db.Command.CommandText = SQL;
                
                db.AddParam("@NOME", SqlDbType.VarChar, usu.Nome);
                db.AddParam("@EMAIL", SqlDbType.VarChar, usu.Email);
                db.AddParam("@SITUACAO", SqlDbType.VarChar, usu.Status);
                db.AddParam("@LOTACAO", SqlDbType.VarChar, usu.Lotacao);
                
                #endregion
                
                if (!db.Transacao())
                    throw new Exception();


                db.Command.Parameters.Clear();

                SQL = string.Empty;
                SQL = @"INSERT INTO TB_AUDITORIA
                                   (ACAO,
									TABELA,
									DESCRICAO,
									USUARIO,
									DATA)
                             VALUES
                                   ('INSERT',
                                   'TB_USUARIO',
                                    ((SELECT CAST(MAX(ID_USUARIO) AS VARCHAR) FROM TB_USUARIO)
                                    + ' | ' + UPPER(@NOME)
                                    + ' | ' + UPPER(@EMAIL)
                                    + ' | ' + UPPER(@SITUACAO)
                                    + ' | ' + UPPER(@LOTACAO)),                        
                                    UPPER(@USUARIO),
                                    GETDATE())";

                db.Command.CommandText = SQL;
                
                db.AddParam("@NOME", SqlDbType.VarChar, usu.Nome);
                db.AddParam("@EMAIL", SqlDbType.VarChar, usu.Email);
                db.AddParam("@SITUACAO", SqlDbType.VarChar, usu.Status);
                db.AddParam("@LOTACAO", SqlDbType.VarChar, usu.Lotacao);
                db.AddParam("@USUARIO", SqlDbType.VarChar, usuarioLogado);
                
                if (!db.Transacao())
                    throw new Exception();


                if (!TrustX.Security.Usuario.CriarUsuario(usu.Nome, "piox123456", usu.Email))
                    throw new System.Exception();
                string[] usuarios = new string[1];
                usuarios[0] = usu.Nome;                
                if (!TrustX.Security.Regras.AddUsuariosRegra(usuarios, "Secretaria"))
                    throw new System.Exception();

                string corpo = string.Format(@"<html>
                                                <head>
                                                    <title>AcadPos</title>
                                                </head>
                                                <body style='font-family:Verdana; font-size:10pt;'>
                                                    <b>Portal de Serviços - AcadPos</b><br />
                                                    <p>O AcadPos recebeu a sua solicitação de criação de usuário.</p>
                                                    <p>Abaixo segue os dados de acesso.</p>
                                                    <br />
                                                    <p>Usuário:&nbsp;&nbsp;<b>{0}</b><br />Senha:&nbsp;&nbsp;<b>piox123456</b></p>                                                    
                                                    <br />
                                                    <br />                                                    
                                                </body>
                                                </html>", usu.Nome);

                if (!TrustX.IO.Suporte.EnviarEmail(usu.Email, "Cadastro de Usuário", corpo, string.Empty))
                    throw new System.Exception();

                transacao.Commit();
                return true;
            }
            catch (System.Exception ex)
            {
                transacao.Rollback();
                throw new System.Exception(ex.Message.ToString());
            }
            finally
            {
                db.FecharConexao();
            }
        }
        #endregion

        #region Alterar Usuário
        public bool alterarUsuario(Usuario usuario, string usuarioLogado)
        {
            string sql = "";
            DataAccess db = new DataAccess(sql);
            SqlTransaction transacao;
            db.AbrirConexao();
            transacao = db.BeginTransaction();
            try
            {
                db.Command.Transaction = transacao;
                #region Alterar Usuário
                string SQL = @"UPDATE TB_USUARIO
                                   SET NOME = @NOME                                      
                                      ,EMAIL = @EMAIL
                                      ,SITUACAO = @SITUACAO
                                      ,LOTACAO = @LOTACAO
                              WHERE ID_USUARIO = @ID_USUARIO";

                db.Command.CommandText = SQL;
                db.AddParam("@NOME", SqlDbType.VarChar, usuario.Nome);
                db.AddParam("@EMAIL", SqlDbType.VarChar, usuario.Email);
                db.AddParam("@SITUACAO", SqlDbType.VarChar, usuario.Status);
                db.AddParam("@LOTACAO", SqlDbType.VarChar, usuario.Lotacao);
                db.AddParam("@ID_USUARIO", SqlDbType.Int, usuario.Codigo);
                #endregion
                if (!db.Transacao())
                    throw new Exception();

                db.Command.Parameters.Clear();

                #region Registrar Auditoria
                string SQL1 = @"INSERT INTO TB_AUDITORIA
                                   (ACAO,
									TABELA,
									DESCRICAO,
									USUARIO,
									DATA)
                             VALUES
                                   ('UPDATE',
                                   'TB_USUARIO',
                                    (CAST(@ID_USUARIO AS VARCHAR)
                                    + ' | ' + UPPER(@NOME)
                                    + ' | ' + UPPER(@EMAIL)
                                    + ' | ' + UPPER(@SITUACAO)
                                    + ' | ' + UPPER(@LOTACAO)),                        
                                    UPPER(@USUARIO),
                                    GETDATE())";

                db.Command.CommandText = SQL1;

                db.AddParam("@NOME", SqlDbType.VarChar, usuario.Nome);
                db.AddParam("@EMAIL", SqlDbType.VarChar, usuario.Email);
                db.AddParam("@SITUACAO", SqlDbType.VarChar, usuario.Status);
                db.AddParam("@LOTACAO", SqlDbType.VarChar, usuario.Lotacao);
                db.AddParam("@ID_USUARIO", SqlDbType.Int, usuario.Codigo);
                db.AddParam("@USUARIO", SqlDbType.VarChar, usuarioLogado);

                #endregion

                if (!db.Transacao())
                    throw new Exception();

                transacao.Commit();
                return true;
            }
            catch (System.Exception ex)
            {
                transacao.Rollback();
                throw new System.Exception(ex.Message.ToString());
            }
            finally
            {
                db.FecharConexao();
            }
        }
        #endregion

        #region Excluir Usuário
        public bool excluirUsuario(Usuario usuario, string usuarioLogado)
        {
            string sql = "";
            DataAccess db = new DataAccess(sql);
            SqlTransaction transacao;
            db.AbrirConexao();
            transacao = db.BeginTransaction();
            try
            {
                db.Command.Transaction = transacao;
                #region Excluir Usuário
                string SQL = @"DELETE FROM TB_USUARIO
                            WHERE ID_USUARIO = @ID_USUARIO";
                db.Command.CommandText = SQL;
                db.AddParam("@ID_USUARIO", SqlDbType.VarChar, usuario.Codigo);

                #endregion
                if (!db.Transacao())
                    throw new Exception();
                
                if (!TrustX.Security.Usuario.ExcluirUsuario(usuario.Nome))
                    throw new System.Exception();

                db.Command.Parameters.Clear();

                #region Registrar Auditoria
                string SQL1 = @"INSERT INTO TB_AUDITORIA
                                   (ACAO,
									TABELA,
									DESCRICAO,
									USUARIO,
									DATA)
                             VALUES
                                   ('DELETE',
                                   'TB_USUARIO',
                                    (CAST(@ID_USUARIO AS VARCHAR)
                                    + ' | ' + UPPER(@NOME)
                                    + ' | ' + UPPER(@EMAIL)
                                    + ' | ' + UPPER(@SITUACAO)
                                    + ' | ' + UPPER(@LOTACAO)),                        
                                    UPPER(@USUARIO),
                                    GETDATE())";

                db.Command.CommandText = SQL1;

                db.AddParam("@NOME", SqlDbType.VarChar, usuario.Nome);
                db.AddParam("@EMAIL", SqlDbType.VarChar, usuario.Email);
                db.AddParam("@SITUACAO", SqlDbType.VarChar, usuario.Status);
                db.AddParam("@LOTACAO", SqlDbType.VarChar, usuario.Lotacao);
                db.AddParam("@ID_USUARIO", SqlDbType.Int, usuario.Codigo);
                db.AddParam("@USUARIO", SqlDbType.VarChar, usuarioLogado);

                #endregion

                if (!db.Transacao())
                    throw new Exception();

                transacao.Commit();
                return true;
            }
            catch (System.Exception ex)
            {
                transacao.Rollback();
                throw new System.Exception(ex.Message.ToString());
            }
            finally
            {
                db.FecharConexao();
            }
        }
        #endregion

        #region Obter Usuários do Menbership
        public DataTable obterUsuariosMenbership()
        {
            string SQL = @"SELECT au.UserName AS USERNAME,
                                       am.Email AS EMAIL,
                                       CASE am.IsLockedOut
                                            WHEN 1 THEN 'BLOQUEADO'
                                            WHEN 0 THEN 'DESBLOQUEADO'
                                       END AS SITUACAO
                                FROM   aspnet_Membership am
                                       JOIN aspnet_Users au
                                            ON  au.UserId = am.UserId
                                       JOIN aspnet_Applications aa
                                            ON  aa.ApplicationId = au.ApplicationId
                                WHERE  aa.ApplicationName = '/ACADPOS'";
            DataAccess db = new DataAccess(SQL);
            return db.GetDataTable();
        }
        #endregion

        #region Bloquear Usuário
        public bool bloquearUsuario(string pUSerName)
        {
            string SQL = @"UPDATE am
                            SET    IsLockedOut = 1
                            FROM   aspnet_Membership am
                                   JOIN aspnet_Applications aa
                                        ON  aa.ApplicationId = am.ApplicationId
                                   JOIN aspnet_Users au
                                        ON  au.ApplicationId = aa.ApplicationId
                                        AND au.UserId = am.UserId 
                            WHERE aa.ApplicationName = '/ACADPOS' 
                            AND au.UserName = @USERNAME ";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@USERNAME", SqlDbType.VarChar, pUSerName);
            return db.ExecuteNonQuery();
        }
        #endregion

        #region Desbloquear Usuário
        public bool desbloquearUsuario(string pUSerName)
        {
            string SQL = @"UPDATE am
                            SET    IsLockedOut = 0
                            FROM   aspnet_Membership am
                                   JOIN aspnet_Applications aa
                                        ON  aa.ApplicationId = am.ApplicationId
                                   JOIN aspnet_Users au
                                        ON  au.ApplicationId = aa.ApplicationId
                                        AND au.UserId = am.UserId 
                            WHERE aa.ApplicationName = '/ACADPOS' 
                            AND au.UserName = @USERNAME ";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@USERNAME", SqlDbType.VarChar, pUSerName);
            return db.ExecuteNonQuery();
        }
        #endregion

        #region Obter Estados Alocados por Usuário
        public DataTable obterEstadosXUsuario(int pCodUser)
        {
            string SQL = @"SELECT tue.ID_USUARIO_ESTADO,
                                  tue.ID_USUARIO,
                                  tue.UF,
                                  tue.SITUACAO
                            FROM   TB_USUARIO_ESTADO tue
                            WHERE tue.ID_USUARIO = @ID_USUARIO
                            ORDER BY tue.UF";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_USUARIO", SqlDbType.Int, pCodUser);
            return db.GetDataTable();
        }
        #endregion

        #region Alocar Estado ao Usuário
        public bool alocarEstado(Estado estado, int pCodUser)
        {
            string SQL = @"INSERT INTO TB_USUARIO_ESTADO
                              (
                                ID_USUARIO,
                                UF,
                                SITUACAO,
                                DATA_CADASTRO
                              )
                            VALUES
                              (
                                @ID_USUARIO,
                                @UF,
                                @SITUACAO,
                                GETDATE()
                              )";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_USUARIO", SqlDbType.Int, pCodUser);
            db.AddParam("@UF", SqlDbType.VarChar, estado.Descricao);
            db.AddParam("@SITUACAO", SqlDbType.VarChar, estado.Status);
            return db.ExecuteNonQuery();
        }
        #endregion        

        #region Excluir Estado alocado ao Usuário 
        public bool excluirEstadoXUsuario(int pDataKey)
        {
            string SQL = @"DELETE 
                            FROM   TB_USUARIO_ESTADO
                            WHERE  ID_USUARIO_ESTADO = @ID_USUARIO_ESTADO ";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_USUARIO_ESTADO", SqlDbType.Int, pDataKey);            
            return db.ExecuteNonQuery();
        }
        #endregion

        #region Verifica se estado já foi alocado para determinado Usuário
        public bool verificaEstadoJaAlocado(int pCodUser, string pUF)
        {
            string SQL = @"SELECT tue.ID_USUARIO_ESTADO
                            FROM   TB_USUARIO_ESTADO tue
                            WHERE  tue.ID_USUARIO = @ID_USUARIO
                                   AND tue.UF = @UF";
            DataAccess db = new DataAccess(SQL);
            db.AddParam("@ID_USUARIO", SqlDbType.Int, pCodUser);
            db.AddParam("@UF", SqlDbType.VarChar, pUF);
            return db.GetDataTable().Rows.Count > 0;
        }
        #endregion
    }
}
